<?php
/**
 * class Order_model 订单管理模型 
 * @author yw
 * 2017年6月6日17:23:24
 */
class Order_model extends BS_Model
{
	/**
	 * [add 添加一条订单记录]
	 * @param [array] $params [生成订单的数组]
	 * @return  新插入行的id ,插入失败返回false
	 */
	public function addOrder($data)
	{
		$query = $this->db->insert('order', $data);
		if($query === false)
		{
			return false;
		}
        return $this->db->insert_id();
	}
	
	/**
	 * [getOrderByStatus 后台查询与统计]
	 * @param  [int] $start_time [开始时间]
	 * @param  [int] $end_time   [结束时间]
	 * @param  [string] $status  [1 未发货 2 已发货 5 已退押金]
	 * @param  [string] $orderid [订单id]
	 * @param  [string] $limit [每页显示的条数]
	 * @param  [string] $offset [偏移量]
	 * @return [array] 
	 */
	public function getOrderByQuery($start_time,$end_time,$status=null,$orderid,$limit=null,$offset=null)
	{
		$this->db->select('A.order_id,A.address,A.status,A.amount,A.price,A.paytype,A.leave,A.create_time,A.logistics,A.express');
		$this->db->from('order as A');
		$where = array();
		!empty($start_time) ? $where = array('A.create_time >='=>$start_time,'A.create_time <='=>$end_time+86399) :'';
		!empty($status) ? $where['status'] = $status : '';
		if(!empty($orderid)){
			if(preg_match("/^1[34578]{1}\d{9}$/",$orderid))
			{  
			    $where['A.address like'] = '%'.$orderid.'%';//根据手机号搜索
			}else{  
			    $where['A.order_id'] = $orderid;//根据订单号搜索
			} 
		}
		$this->db->where($where);
		$this->db->order_by('A.create_time desc');
		if(isset($offset) && isset($limit))
		{
			$this->db->limit($limit, $offset);
		}
		$query = $this->db->get();
		if($query->num_rows()>0)
		{
			return $query->result_array();
		}
		return array();
	}
	/**
	 * [countOrder 统计符合条件的有多少条]
	 * @param  [type] $start_time [description]
	 * @param  [type] $end_time   [description]
	 * @param  [type] $status     [description]
	 * @param  [type] $orderid    [description]
	 * @return [type]             [description]
	 */
	public function countOrder($start_time,$end_time,$status,$orderid)
	{
		$where = array();
		!empty($end_time) ? $where = array('order.create_time >='=>$start_time,'order.create_time <='=>$end_time+86399) :'';
		!empty($status) ? $where['status'] = $status : '';
		!empty($orderid) ? $where['order.order_id'] = $orderid :'';
		$this->db->from('order');
		$this->db->where($where);
		return $this->db->count_all_results();
	}

	/**
	 * [getOrderByUid 根据用户id 查订单]
	 * @param  [int] $uid    [用户id]
	 * @return [array]
	 */ 
	public function getOrderByUid($uid)
	{
		$query = $this->db->from('order as A')->select('A.order_id,A.status,A.amount,A.price,A.create_time')->where('uid',$uid)->order_by('A.create_time DESC')->get();
		if($query->num_rows() > 0)
		{
			return $query->result_array();
		}
		return array();
	}
	/**
	 * [getOrderById 根据order_id获取订单详情]
	 * @param  [type] $order_id [description]
	 * @return [type]           [description]
	 */
	public function getOrderById($order_id)
	{
		$this->db->from('order as A');
		$this->db->select('*');
		$this->db->where('order_id',$order_id);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			return $query->row_array();
		}
		return array();
	}

	/**
	 * [getOrderByUid 根据用户id 查订单数量]
	 * @param  [int] $uid    [用户id]
	 * @return [int]
	 */
	public function getOrderCountByUid($uid)
	{
		$this->db->from('order');
		$where = array('uid'=>$uid);
		$status = array(1,2,5);//状态
		$this->db->where($where);
		$this->db->where_in('status',$status);
		$query = $this->db->get();
		$count = 0;
		if($query->num_rows() > 0)
		{
			$data= $query->result_array();
			foreach($data as $value)
			{
				$count+=$value['amount'];
			}
		}
		return $count;
	}

	/**
	 * 根据下单起止时间获取待退款列表
	 * @param  [int] $starttime [起始时间时间戳]
	 * @param  [int] $endtime   [终止时间时间戳]
	 * @param  [int] $offset [limit偏移量] 
	 * @param  [int] $limit   [选取多少条记录]
	 * @return [array]            [查询不到数据则返回空数组]
	 */
	// public function getOrderToRefund($starttime = null, $endtime = null, $offset = null, $limit = null)
	// {
	// 	$where['order.status'] = 1;
	// 	isset($starttime) && $where['order.create_time >'] = $starttime;
	// 	isset($endtime) && $where['order.create_time <'] = $endtime;
	// 	$this->db->select('order.order_id,users.mobile,order.status,order.return_money,order.amount,order.price,order.paytype,order.create_time');
	// 	$this->db->from('order');
	// 	$this->db->where($where);
	// 	$this->db->join('users', 'users.uid=order.uid');
	// 	$this->db->order_by('order.create_time desc');
	// 	if(isset($offset) && isset($limit))
	// 	{
	// 		$this->db->limit($limit, $offset);
	// 	}
	// 	$query = $this->db->get();
	// 	if($query->num_rows() > 0)
	// 	{
	// 		return $query->result_array();
	// 	}
	// 	return array();
	// }
	/**
	 * 根据活动获取退款列表
	 * @param  [int] $starttime [起始时间时间戳]
	 * @param  [int] $endtime   [终止时间时间戳]
	 * @param  [int] $offset [limit偏移量] 
	 * @param  [int] $limit   [选取多少条记录]
	 * @return [array]            [查询不到数据则返回空数组]
	 */
	public function getOrderToRefund($params = null, $offset = null, $limit = null){
		
		$date = $params['datatime']*24*3600;
		$starttime = $params['starttime'] - $date;
		$endtime = ($params['endtime'] == $params['starttime'])?$endtime = ($params['endtime']+24*3600) - $date:$endtime = $params['endtime'] - $date;
		$status = array(1,2);//状态
		$sql = "(order_detail.last_return_time >= '".$starttime."' and order_detail.last_return_time <= '".$endtime."')";
		$this->db->select('order.order_id,order_detail.order_detail_id,users.mobile,users.username,order_detail.status,order_detail.return_money,order_detail.amount,order_detail.price,order_detail.last_return_time,users.cardid,banklist.rhname,banklist.rhcode,order.paytype,order.create_time');
		$this->db->from('order');
		$this->db->join('order_detail', 'order.order_id=order_detail.order_id');
		$this->db->join('users', 'users.uid=order.uid');
		$this->db->join('banklist', 'users.bankid=banklist.id');
		if(isset($params['sourch'])){
			$query = "(users.mobile like '%".trim($params['sourch'])."%' or users.username like '%".trim($params['sourch'])."%' or users.cardid like '%".trim($params['sourch'])."%' )";
			$this->db->where($query);
		}
		$this->db->where('order_detail.activity_id',$params['activity_id']);
		$this->db->where_in('order_detail.status',$status);
		$this->db->where('order_detail.last_return_time <> 0');
		$this->db->where($sql);
		$this->db->order_by('users.mobile desc');
		if(isset($offset) && isset($limit))
		{
			$this->db->limit($limit, $offset);
		}
		$query = $this->db->get();

		if($query->num_rows() > 0)
		{   
			$data = $query->result_array();
			
			foreach ($data as $key => $value) {
				$data[$key]['last_return_time'] = $value['last_return_time'] + $date;
			}
			return $data;
		}
        
		return array();
	}

	public function getOrderToTotalrefund($params = null, $offset = null, $limit = null){
		
		$date = $params['datatime']*24*3600;
		$starttime = $params['starttime'] - $date;
		$endtime = ($params['endtime'] == $params['starttime'])?$endtime = ($params['endtime']+24*3600) - $date:$endtime = $params['endtime'] - $date;
		$status = array(1,2);//状态
		$sql = "(order_detail.last_return_time >= '".$starttime."' and order_detail.last_return_time <= '".$endtime."')";
		$this->db->select('users.mobile,sum(order_detail.price/"'.$params['activity_period'].'") as price,users.username,users.cardid,banklist.rhname,banklist.rhcode');
		$this->db->from('order');
		$this->db->join('order_detail', 'order.order_id=order_detail.order_id');
		$this->db->join('users', 'users.uid=order.uid');
		$this->db->join('banklist', 'users.bankid=banklist.id');
		if(isset($params['sourch'])){
			$query = "(users.mobile like '%".trim($params['sourch'])."%' or users.username like '%".trim($params['sourch'])."%' or users.cardid like '%".trim($params['sourch'])."%' )";
			$this->db->where($query);
		}
		$this->db->where('order_detail.activity_id',$params['activity_id']);
		$this->db->where_in('order_detail.status',$status);
		$this->db->where('order_detail.last_return_time <> 0');
		$this->db->where($sql);
		$this->db->group_by("users.mobile");
		$this->db->order_by('users.mobile desc');
		if(isset($offset) && isset($limit))
		{
			$this->db->limit($limit, $offset);
		}
		$query = $this->db->get();
		if($query->num_rows() > 0)
		{   
			$data = $query->result_array();
			return $data;
		}
        
		return array();
	}

	public function  getrefundedOrderToRefund($params = null,$offset = null, $limit = null){
		$this->db->select('order.order_id,order.create_time,order_detail.order_detail_id,users.mobile,users.username,order_detail.status,order_detail.return_money,order_detail.amount,order_detail.price,order.paytype,order_detail.last_return_time');
		$this->db->from('order');
		$this->db->join('order_detail', 'order.order_id=order_detail.order_id');
		$this->db->join('users', 'users.uid=order.uid');
		if(!empty($params['activity_id'])){
			$this->db->where('order_detail.activity_id',$params['activity_id']);
		}
		if(isset($params['sourch'])){
			$query = "(users.mobile like '%".trim($params['sourch'])."%' or users.username like '%".trim($params['sourch'])."%')";
			$this->db->where($query);
		}
		$this->db->where('order_detail.return_money > 0');
		if(isset($offset) && isset($limit))
		{
			$this->db->limit($limit, $offset);
		}
		$query = $this->db->get();

		if($query->num_rows() > 0)
		{
			return $query->result_array();
		}

		return array();
	}
	/**
	 * 根据时间范围返回待退押金订单总数
	 * @param [int] $starttime [起始时间时间戳]
	 * @param [int] $endtime   [终止时间时间戳]
	 * @return [int]           [待退押金订单总数]
	 */
	public function CountOrderToRefund($params = null)
	{	
		
		$date = $params['datatime']*24*3600;
		$starttime = $params['starttime'] - $date;
		$endtime = ($params['endtime'] == $params['starttime'])?$endtime = ($params['endtime']+24*3600) - $date:$endtime = $params['endtime'] - $date;
		$status = array(1,2);//状态
		$sql = "(order_detail.last_return_time >= '".$starttime."' and order_detail.last_return_time <= '".$endtime."')";
		
		$this->db->from('order');
		$this->db->join('order_detail', 'order.order_id=order_detail.order_id');
		$this->db->join('users', 'users.uid=order.uid');
		$this->db->join('banklist', 'users.bankid=banklist.id');
		if(isset($params['sourch'])){
			$query = "(users.mobile like '%".trim($params['sourch'])."%' or users.username like '%".trim($params['sourch'])."%' or users.cardid like '%".trim($params['sourch'])."%' )";
			$this->db->where($query);
		}
		$this->db->where('order_detail.activity_id',$params['activity_id']);
		$this->db->where_in('order_detail.status',$status);
		$this->db->where('order_detail.last_return_time <> 0');
		$this->db->where($sql);
	    return $this->db->count_all_results();	
		
	}
	public function CountOrderToRefunded($params)
	{
		$this->db->from('order');
		$this->db->join('order_detail', 'order.order_id=order_detail.order_id');
		$this->db->join('users', 'users.uid=order.uid');
		if(!empty($params['activity_id'])){
			$this->db->where('order_detail.activity_id',$params['activity_id']);
		}
		if(isset($params['sourch'])){
			$query = "(users.mobile like '%".trim($params['sourch'])."%' or users.username like '%".trim($params['sourch'])."%' or users.cardid like '%".trim($params['sourch'])."%' )";
			$this->db->where($query);
		}
		$this->db->where('order_detail.return_money > 0');
	    return $this->db->count_all_results();		
	}
	/**
	 * [getOrderByOrderid description]
	 * @param  [type] $orderid [description]
	 * @return [type]          [description]
	 */
	public function getOrderByOrderid($orderid)
	{
		$query = $this->db->from('order as A')
				 	      ->select('A.order_id,A.create_time,A.status,A.paytype,A.amount,A.price,A.address,A.leave,A.uid,A.logistics,A.express')
				 	      ->where('A.order_id',$orderid)
				 	      ->get();
		$info = $query->num_rows() > 0 ? $query->row_array() : array();

		if(!empty($info) && !empty($info['address']))
		{
			$addressInfo = json_decode($info['address']);
			$this->load->model('Zone_model');
			$res = $this->Zone_model->getZonesStringById($addressInfo->district);
			//组装数据
			$info['username'] = $addressInfo->consignee;//收货人姓名
			$info['address'] = $res.$addressInfo->address;//详细地址
			$info['mobile'] = $addressInfo->mobile;//收货人手机号码
		}
		return $info;
	}

	/**
	 * 根据订单id和参数数组更新订单
	 * @param  int $order_id 订单号
	 * @param  array $arr      参数数组(键值对)
	 * @return bool           成功返回true,失败返回false
	 */
	public function updateOrderByOrderid($order_id, $arr)
	{
		$this->db->set($arr);
		$this->db->where('order_id', $order_id);
		$result = $this->db->update('order');
		return $result;
	}
	/**
	 * [getOrderTimeoutByTime 查询订单已经超时的]
	 * @param  [type] $time [过期时间]
	 * @return [type]       [description]
	 */
	public function getOrderTimeoutByTime($starttime,$time,$limit)
	{
		$where = array('create_time >='=>$starttime,'create_time <=' => $time,'status' => 3);
		$query = $this->db->from('order')->where($where)->order_by('create_time ASC')->limit($limit)->get();
		// echo $this->db->last_query();die;
		return $query->num_rows() > 0 ? $query->result_array() : array();
	}
	/**
	 * [updateOrderStatusByOrderId 修改已超时的订单状态]
	 * @param  [type] $orderid [订单id]
	 * @return [type]          [s受影响的行数]
	 */
	public function updateOrderStatusByOrderId($orderid)
	{
		$arr = array('status'=>4,'update_time'=>time());
		$this->db->set($arr);
		$this->db->where_in('order_id',$orderid);
		$this->db->update('order');
		return $this->db->affected_rows();
	}
	/**
	 * [getOrderToShipment 待发货列表]
	 * @param  [type] $start_time [开始时间]
	 * @param  [type] $end_time   [结束时间]
	 * @return [type]    
	 */
	public function getOrderToShipment()
	{
		$where = array();
		$where['A.create_time <='] = strtotime(date("Y-m-d",strtotime("-1 day")))+86399;
		$where['A.status'] = 1;
		$query = $this->db->from('order as A')
					->select('A.order_id,A.address')
					->where($where)
					->get();
					
		$data = $query->num_rows() > 0 ? $query->result_array() : array();
		if(empty($data))return array();//没有订单要处理
		$orderid = array();
		foreach ($data as $k_1 => $v_1) 
		{
			$orderid[]=$v_1['order_id'];
		}
		$query1 = $this->db->from('order_detail as A')
							->select('A.order_id,A.order_detail_id,A.goods_id,B.name,count(*) as total')
							->where_in('A.order_id',$orderid)
							->join('goods as B','on B.goods_id = A.goods_id')
							->group_by('order_id,goods_id')
							->get();
		$detail = $query1->num_rows() ? $query1->result_array() : array();
		$this->load->model('Zone_model');
		foreach ($data as $k_2 => $v_2) {
			$addressInfo = json_decode($v_2['address']);
			$res = $this->Zone_model->getZonesStringById($addressInfo->district);
			//组装数据
			$data[$k_2]['username'] = $addressInfo->consignee;
			$data[$k_2]['address'] = $res.$addressInfo->address;
			$data[$k_2]['mobile'] = $addressInfo->mobile;
		}
	
		foreach ($data as $k_3 => $v_3) {
			foreach ($detail as $k_4 => $v_4) {
				if($v_4['order_id'] == $v_3['order_id'])
				{
					if(!isset($data[$k_3]['goodsdetail']))
					{
						$data[$k_3]['goodsdetail'] = array();
					}
					$data[$k_3]['goodsdetail'][] = $v_4['name'].' * '.$v_4['total'];
				}
			}
			$data[$k_3]['det'] = implode(',', $data[$k_3]['goodsdetail']);
			unset($data[$k_3]['goodsdetail']);
		}
		return $data;
	}

	/**
	 * [getOrderData 查询单条数据或者根据订单id拿出所有子订单]
	 * @param  [int] $orderid [订单id]
	 * @return [array]        
	 */
	public function getRowOrderData($orderid,$type)
	{
		if($type==1)
		{
			//返回已绑完套装码但没有绑定物流单号的订单
			$data_where =  array('A.status'=>1,'A.logistics'=>'','B.suite_code !='=>'');
			$count_where = array('A.status'=>1,'B.suite_code !='=>'');
			
			$rows = $this->getoderDataByStatus($data_where);//要绑定物流的数据
			$count =$this->countOrderDataByStatus($count_where);//要处理的总数
			$undone = $this->getOrderNoSuiteCodeData($data_where);//未处理总数
			$rows['lock'] = 0;
			$rows['count'] = $count;//总数
			$rows['undone'] = $count-($count-$undone);//未完成的
			$rows['dispose'] = $count-$undone;//已完成的
					
			return $rows;
		}
		if(!isset($orderid))
		{
			$time = strtotime(date("Y-m-d",strtotime("-1 day")))+86399;//当前凌晨12点前 

			$lock_where = array('A.status'=>1,'A.is_lock'=>2,'A.create_time <=' =>$time);//锁单数量
			$data_where = array('A.status'=>1,'A.is_lock'=>1,'A.create_time <=' =>$time,'B.suite_code ='=>'','B.status'=>1);//代取
			$suite_where =  array('A.status'=>1,'A.create_time <='=>$time,'B.suite_code'=>'');//套装码未绑定总数
			$count_where = array('A.status'=>1,'A.create_time <=' =>$time);//代取总数

			$lock = $this->getLockNumber($lock_where);//被锁订单数量
			$rows = $this->getoderDataByStatus($data_where);//数据
			$count = $this->countOrderDataByStatus($count_where);//总数量
			$undone = $this->getOrderNoSuiteCodeData($suite_where);//未处理完的
			
			//正在操作的锁单
			if(!empty($rows))
			{
				$arr = array('is_lock'=>2,'update_time'=>time());
				$this->updateOrderByOrderid($rows['order_id'],$arr);
			}
			$rows['lock'] = $lock;
			$rows['count'] = $count;
			$rows['dispose'] = $count-$undone;//处理完成的数量
			$rows['undone'] = $undone;//未处理完成的数量
			return $rows;
		}
		else
		{
			//查询子订单
			$where = array('B.order_id'=>$orderid,'B.suite_code = '=>'');
			$query = $this->db->from('order_detail as B')
							  ->select('B.order_detail_id,B.activity_id,G.name')
							  ->join('goods as G','on B.goods_id=G.goods_id')
					 		  ->where($where)
					 		  ->get();
			$orderDetail = $query->num_rows() > 0 ? $query->result_array() : array();
			if(empty($orderDetail)) return array();
			return $orderDetail;
		}
		return array();
	}
	/**
	 * [getLockNumber 查看被锁的订单数量]
	 * @return [number] 
	 */
	public function getLockNumber($where)
	{
		$number = $this->db->from('order as A')
						   ->select('A.order_id')
						   ->join('order_detail as B','on A.order_id = B.order_id','LEFT')
						   ->where($where)
						   ->group_by('A.order_id')
						   ->count_all_results();
		return $number;
	}
	/**
	 * [getoderDataByStatus 根据状态拿出一条数据]
	 * @return [type] [description]
	 */
	public function getoderDataByStatus($where)
	{
		$query = $this->db->from('order as A')
						  ->select('A.order_id,A.create_time,A.address,A.amount')
						  ->join('order_detail as B','on A.order_id = B.order_id','LEFT')
						  ->where($where)
						  ->order_by('A.create_time asc')
						  ->limit(1)
						  ->get();
		$rows = $query->num_rows() > 0 ? $query->row_array() : array();
		if(empty($rows)) return $rows;

		$address = json_decode($rows['address']);
		$rows['username'] = $address->consignee;
		unset($rows['address']);
		return $rows;
	}
	/**
	 * [getdata 获取未处理套装码的订单总数量]
	 * @return [type] [description]
	 */
	public function getOrderNoSuiteCodeData($where)
	{
		return $this->db->from('order as A')
						->select('A.order_id')
						->join('order_detail as B','on A.order_id = B.order_id')
						->where($where)
						->group_by('A.order_id')
						->count_all_results();
	}

	/**
	 * [countOrderDataByStatus //根据状态统计总数量]
	 * @return [type] [description]
	 */
	public function countOrderDataByStatus($where)
	{
		
		$number = $this->db->from('order as A')
						   ->select('A.order_id')
						   ->join('order_detail as B','on A.order_id = B.order_id','LEFT')
						   ->where($where)
						   ->group_by('A.order_id')
						   ->count_all_results();
		return $number;			 
	}
	/**
	 * [updateLogistics 修改物流单号]
	 * @param  [type] $order_id [订单id]
	 * @param  [type] $number   [物流单号]
	 * @param  [type] $express   [物流公司名]
	 * @return [type]   null=订单不存在 false=物流单号已存在 true=修改成功  
	 */
	public function updateLogistics($order_id,$number,$express)
	{
		$query = $this->db->from('order')->where('order_id',$order_id)->get();
		$orderdata = $query->num_rows() > 0 ? $query->row_array() : array();
		if(empty($orderdata)) return null;

		if(empty($orderdata['logistics']))
		{
			$arr = array('logistics'=>$number,'express'=>$express,'update_time'=>time(),'status'=>2);
			return $this->updateOrderByOrderid($order_id,$arr);//修改物流单号
		}
		return false;
	}
	/**
	 * [updateOrderStatus 解锁订单]
	 * @param  [type] $params [description]
	 * @return [type]      [description]
	 */
	public function updateOrderStatus($params)
	{
		$arr = array('is_lock'=>1,'update_time'=>time());
		$this->db->set($arr);
		$this->db->where($params);
		$this->db->update('order');
	}
}